package com.szmirren.common;

import java.sql.*;
import java.util.*;

import com.mongodb.MongoClient;
import com.mongodb.MongoCredential;
import com.mongodb.ServerAddress;
import com.mongodb.client.ListCollectionsIterable;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoCursor;
import com.mongodb.client.MongoDatabase;
import com.szmirren.entity.MongoFieldAttribute;
import com.szmirren.entity.TableContent;
import com.szmirren.models.*;
import com.szmirren.options.DatabaseConfig;
import freemarker.template.utility.StringUtil;
import org.bson.Document;
import org.sqlite.util.StringUtils;

/**
 * 数据库工具
 * 
 * @author Mirren
 *
 */
public class DBUtil {
	private static final int DB_CONNECTION_TIMEOUTS_SECONDS = 1;

	/**
	 * 获得数据库连接
	 * 
	 * @param config
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static Connection getConnection(DatabaseConfig config) throws ClassNotFoundException, SQLException {
		DriverManager.setLoginTimeout(DB_CONNECTION_TIMEOUTS_SECONDS);
		DBType dbType = DBType.valueOf(config.getDbType());
		Class.forName(dbType.getDriverClass());
		String url = getConnectionURL(config);
		if (dbType == DBType.Oracle) {
			Connection connection;
			try {
				connection = DriverManager.getConnection(url, config.getUserName(), config.getUserPwd());
			} catch (Exception e) {
				String oracle = String.format(DBType.OracleServiceName.getConnectionUrlPattern(), config.getConnURL(), config.getListenPort(),
						config.getDbName());
				connection = DriverManager.getConnection(oracle, config.getUserName(), config.getUserPwd());
			}
			return connection;
		} else {
			return DriverManager.getConnection(url, config.getUserName(), config.getUserPwd());
		}
	}

	/**
	 * 获得数据库连接URL
	 * 
	 * @param dbConfig
	 * @return
	 * @throws ClassNotFoundException
	 */
	public static String getConnectionURL(DatabaseConfig dbConfig) throws ClassNotFoundException {
		DBType dbType = DBType.valueOf(dbConfig.getDbType());
		String connectionRUL = String.format(dbType.getConnectionUrlPattern(), dbConfig.getConnURL(), dbConfig.getListenPort(),
				dbConfig.getDbName(), dbConfig.getEncoding());
		return connectionRUL;
	}

	/**
	 * 获得数据库的表名
	 * 
	 * @param config
	 * @return
	 * @throws Exception
	 */
	public static List<String> getTableNames(DatabaseConfig config) throws Exception {
		Connection conn = getConnection(config);
		List<String> tables = new ArrayList<>();
		ResultSet rs;
		if (config.getDbType().equalsIgnoreCase(Constant.SQL_SERVER)) {
			// 如果是sqlserver数据库通过查询获得所有表跟视图
			String sql = "select name from sysobjects  where xtype='u' or xtype='v' ";
			rs = conn.createStatement().executeQuery(sql);
			while (rs.next()) {
				tables.add(rs.getString("name"));
			}

		} else {
			// 如果非sqlserver类型的数据库通过JDBC获得所有表跟视图
			DatabaseMetaData md = conn.getMetaData();
			String[] types = {"TABLE", "VIEW"};
			if (config.getDbType().equalsIgnoreCase(Constant.POSTGRE_SQL)) {
				rs = md.getTables(null, null, null, types);
			} else {
				String catalog = conn.getCatalog() == null ? null : conn.getCatalog();
				rs = md.getTables(catalog, config.getUserName().toUpperCase(), "%%", types);
			}
			while (rs.next()) {
				tables.add(rs.getString(3));
			}
		}
		return tables;
	}

	/**
	 * 获得指定表的属性
	 * 
	 * @param config
	 * @param tableName
	 * @return
	 * @throws Exception
	 */
	public static TableContent getTableAttribute(DatabaseConfig config, String tableName) throws Exception {
		Connection conn = getConnection(config);
		TableContent content = new TableContent();
		ResultSet rs;
		DatabaseMetaData md = conn.getMetaData();
		String[] types = {"TABLE", "VIEW"};
		if (config.getDbType().equalsIgnoreCase(Constant.POSTGRE_SQL)) {
			rs = md.getTables(null, null, tableName, types);
		} else {
			String catalog = conn.getCatalog() == null ? null : conn.getCatalog();
			rs = md.getTables(catalog, config.getUserName().toUpperCase(), tableName, types);
		}
		if (rs.next()) {
			content.setTableCat(rs.getString("TABLE_CAT"));
			content.setTableSchem(rs.getString("TABLE_SCHEM"));
			content.setTableName(rs.getString("TABLE_NAME"));
			content.setTableType(rs.getString("TABLE_TYPE"));
			content.setRemarks(rs.getString("REMARKS"));
			content.setTypeCat(rs.getString("TYPE_CAT"));
			content.setTypeSchem(rs.getString("TYPE_SCHEM"));
			content.setTypeName(rs.getString("TYPE_NAME"));
			content.setSelfReferencingColName(rs.getString("SELF_REFERENCING_COL_NAME"));
			content.setRefGeneration(rs.getString("REF_GENERATION"));
		}
		return content;
	}



	/**
	 * 获取表的列属性
	 * 
	 * @param config
	 *          数据库配置文件
	 * @param tableName
	 *          表名
	 * @return
	 * @throws Exception
	 */
	public static List<MongoTypeDto> getMongoColumns(DatabaseConfig config, String tableName) throws Exception {
		MongoDatabase mongoConnection = getMongoConnection(config);
		//获取全部的文档
		MongoCollection<Document> collection = mongoConnection.getCollection(tableName);
		Document document = collection.find().first();
		//获取全部的key
		List<String> list = new ArrayList<>(document.keySet());
		List<MongoTypeDto> attrs = new ArrayList<>();
		if (list != null || list.size() != 0){
			list.stream().forEach(param ->{
				String str = JavaType.mongoDbTypeToJavaType(document.get(param));
				MongoTypeDto mongoTypeDto = new MongoTypeDto();
				mongoTypeDto.setColumn(param);
				mongoTypeDto.setType(str);
				attrs.add(mongoTypeDto);
			});
			if (!list.contains("createTime")){
                MongoTypeDto mongoTypeDto = new MongoTypeDto();
                mongoTypeDto.setColumn("createTime");
                mongoTypeDto.setType("Date");
                attrs.add(mongoTypeDto);
            }
            if (!list.contains("updateTime")){
                MongoTypeDto mongoTypeDto = new MongoTypeDto();
                mongoTypeDto.setColumn("updateTime");
                mongoTypeDto.setType("Date");
                attrs.add(mongoTypeDto);
            }
            if (!list.contains("isDeleted")){
                MongoTypeDto mongoTypeDto = new MongoTypeDto();
                mongoTypeDto.setColumn("isDeleted");
                mongoTypeDto.setType("Integer");
                attrs.add(mongoTypeDto);
            }
		}
		return attrs;
	}

	/**
	 * 获取表的列属性
	 *
	 * @param config
	 *          数据库配置文件
	 * @param tableName
	 *          表名
	 * @return
	 * @throws Exception
	 */
	public static List<TableAttributeEntity> getTableColumns(DatabaseConfig config, String tableName) throws Exception {
		Connection conn = getConnection(config);
		DatabaseMetaData md = conn.getMetaData();

		ResultSet rs = null;
		if (config.getDbType().equalsIgnoreCase(Constant.MYSQL)) {
			rs = md.getColumns(conn.getCatalog(), "%%", tableName, "%%");
		} else {
			rs = md.getColumns(null, null, tableName, null);
		}

		Map<String, TableAttributeEntity> columnMap = new HashMap<>();
		while (rs.next()) {
			TableAttributeEntity attr = new TableAttributeEntity();
			attr.setTdColumnName(rs.getString("COLUMN_NAME"));
			attr.setTdJdbcType(rs.getString("TYPE_NAME"));
			attr.setTdJavaType(JavaType.jdbcTypeToJavaType(rs.getString("TYPE_NAME")));

			attr.setColumnDef(rs.getString("COLUMN_DEF"));
			attr.setRemarks(rs.getString("REMARKS"));
			attr.setColumnSize(rs.getInt("COLUMN_SIZE"));
			attr.setDecimalDigits(rs.getInt("DECIMAL_DIGITS"));
			attr.setOrdinalPosition(rs.getInt("ORDINAL_POSITION"));
			attr.setNullable(rs.getInt("NULLABLE") == 1 ? true : false);
			columnMap.put(rs.getString("COLUMN_NAME"), attr);
		}
		if (columnMap.size() == 0) {
			throw new NullPointerException("从表中获取字段失败!获取不到任何字段!");
		}
		ArrayList<TableAttributeEntity> result = new ArrayList<>(columnMap.values());
		Collections.sort(result);
		return result;
	}

    /**
     * 获取表的列属性
     *
     * @param config
     *          数据库配置文件
     * @param tableName
     *          表名
     * @return
     * @throws Exception
     */
    public static List<TableAttributeBean> getTableColumnsForBean(DatabaseConfig config, String tableName) throws Exception {
        Connection conn = getConnection(config);
        DatabaseMetaData md = conn.getMetaData();
        ResultSet rs = md.getColumns(conn.getCatalog(), "%%", tableName, "%%");
        Map<String, TableAttributeBean> columnMap = new HashMap<>();
        while (rs.next()) {
            TableAttributeBean attr = new TableAttributeBean();
            attr.setTdColumnName(rs.getString("COLUMN_NAME"));
            attr.setTdJdbcType(rs.getString("TYPE_NAME"));
            attr.setTdJavaType(JavaType.jdbcTypeToJavaType(rs.getString("TYPE_NAME")));

            attr.setColumnDef(rs.getString("COLUMN_DEF"));
            attr.setRemarks(rs.getString("REMARKS"));
            attr.setColumnSize(rs.getInt("COLUMN_SIZE"));
            attr.setDecimalDigits(rs.getInt("DECIMAL_DIGITS"));
            attr.setOrdinalPosition(rs.getInt("ORDINAL_POSITION"));
            attr.setNullable(rs.getInt("NULLABLE") == 1 ? true : false);
            columnMap.put(rs.getString("COLUMN_NAME"), attr);
        }
        if (columnMap.size() == 0) {
            throw new NullPointerException("从表中获取字段失败!获取不到任何字段!");
        }
        ArrayList<TableAttributeBean> result = new ArrayList<>(columnMap.values());
        Collections.sort(result);
        return result;
    }

	/**
	 * 获取表的列属性
	 *
	 * @param config
	 *          数据库配置文件
	 * @param tableName
	 *          表名
	 * @return
	 * @throws Exception
	 */
	public static List<TableAttributeDto> getTableColumnsForCreateDto(DatabaseConfig config, String tableName) throws Exception {
		Connection conn = getConnection(config);
		DatabaseMetaData md = conn.getMetaData();

		ResultSet rs = null;
		if (config.getDbType().equalsIgnoreCase(Constant.MYSQL)) {
			rs = md.getColumns(conn.getCatalog(), "%%", tableName, "%%");
		} else {
			rs = md.getColumns(null, null, tableName, null);
		}

		Map<String, TableAttributeDto> columnMap = new HashMap<>();
		while (rs.next()) {
			TableAttributeDto attr = new TableAttributeDto();
			attr.setTdColumnName(rs.getString("COLUMN_NAME"));
			attr.setTdJdbcType(rs.getString("TYPE_NAME"));
			attr.setTdJavaType(JavaType.jdbcTypeToJavaType(rs.getString("TYPE_NAME")));

			attr.setColumnDef(rs.getString("COLUMN_DEF"));
			attr.setRemarks(rs.getString("REMARKS"));
			attr.setColumnSize(rs.getInt("COLUMN_SIZE"));
			attr.setDecimalDigits(rs.getInt("DECIMAL_DIGITS"));
			attr.setOrdinalPosition(rs.getInt("ORDINAL_POSITION"));
			attr.setNullable(rs.getInt("NULLABLE") == 1 ? true : false);
			columnMap.put(rs.getString("COLUMN_NAME"), attr);
		}
		if (columnMap.size() == 0) {
			throw new NullPointerException("从表中获取字段失败!获取不到任何字段!");
		}
		ArrayList<TableAttributeDto> result = new ArrayList<>(columnMap.values());
		Collections.sort(result);
		return result;
	}

	/**
	 * 获得主键名称
	 * 
	 * @param config
	 * @param tableName
	 * @return
	 * @throws Exception
	 */
	public static String getTablePrimaryKey(DatabaseConfig config, String tableName) throws Exception {
		Connection conn = getConnection(config);
		DatabaseMetaData md = conn.getMetaData();
		ResultSet rs = null;
		if (config.getDbType().equalsIgnoreCase(Constant.MYSQL)) {
			rs = md.getPrimaryKeys(conn.getCatalog(), conn.getSchema(), tableName);
		} else {
			rs = md.getPrimaryKeys(null, null, tableName);
		}
		while (rs.next()) {
			return rs.getString("COLUMN_NAME");
		}
		return null;
	}

	/**
	 * 获得Mongodb数据库连接，有密码和无密码
	 *
	 * @param config
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static MongoDatabase getMongoConnection(DatabaseConfig config){
		//获取页面属性
        MongoClient mongoClient = getCurrentMongoClient(config);
        MongoDatabase mongoDatabase = mongoClient.getDatabase(config.getDbName());
		return mongoDatabase;
	}

	/**
	 * 获得Mongodb的全部集合
	 *
	 * @param config
	 * @return
	 * @throws Exception
	 */
	public static List<String> getMongoDbAllCollectionName(DatabaseConfig config)  {
		List<String> list = new ArrayList<>();
        MongoClient mongoClient = getCurrentMongoClient(config);
        MongoDatabase mongoDatabase = mongoClient.getDatabase(config.getDbName());
        //获取所有集合
		ListCollectionsIterable<Document> documents = mongoDatabase.listCollections();
		MongoCursor<Document> iterator = documents.iterator();
		while (iterator.hasNext()){
			String collectionName = iterator.next().getString("name");
			if (collectionName.contains("system.")) continue;
			//获取全部的集合名称
			list.add(collectionName);
		}
		return list;
	}

	//获取mongodbClient
    public static MongoClient getCurrentMongoClient(DatabaseConfig config){
        MongoClient mongoClient;
        if("".equals(config.getUserName()) && "".equals(config.getUserPwd())){
            mongoClient = new MongoClient(config.getConnURL(), Integer.valueOf(config.getListenPort()));
        }else {
            List<ServerAddress> adds = new ArrayList<>();
            //ServerAddress()两个参数分别为 服务器地址 和 端口
            ServerAddress serverAddress = new ServerAddress(config.getConnURL(), Integer.valueOf(config.getListenPort()));
            adds.add(serverAddress);
            List<MongoCredential> credentials = new ArrayList<>();
            //MongoCredential.createScramSha1Credential()三个参数分别为 用户名 数据库名称 密码
            MongoCredential mongoCredential = MongoCredential.createScramSha1Credential(config.getUserName(), config.getDbName(), config.getUserPwd().toCharArray());
            credentials.add(mongoCredential);
            mongoClient = new MongoClient(adds, credentials);
        }
        return mongoClient;
    }



}
